﻿
CREATE PROCEDURE [dbo].[Articles_Split] 	
	@ArticleIds varchar(max), 
	@SourceArticleId int
AS
BEGIN

DECLARE @salesTable TABLE (StoreId smallint, ArticleId int, Date date, Units money, Sales money, GM money, GrossSales money, Price money, Confirmed bit, BonusBuyNumber varchar(100), Deleted bit)

INSERT INTO @salesTable (StoreId, ArticleId, Date, Units, Sales, GM, GrossSales, Price, Confirmed, BonusBuyNumber, Deleted)
Select 
	s.StoreId
	, a.Id ArticleId
	, s.Date
	, CASE WHEN a.Id = @SourceArticleId THEN s.Units * (a.SplitPercentage / 100) ELSE (s.Units * (a.SplitPercentage / 100)) + ISNULL(s2.Units,0) END As Units
	, CASE WHEN a.Id = @SourceArticleId THEN s.Sales * (a.SplitPercentage / 100) ELSE (s.Sales * (a.SplitPercentage / 100)) + ISNULL(s2.Sales,0) END As Sales
	, CASE WHEN a.Id = @SourceArticleId THEN s.GM * (MarginSplitPercentage / 100) ELSE (s.GM * (MarginSplitPercentage / 100)) + ISNULL(s2.GM,0) END As GM
	, CASE WHEN a.Id = @SourceArticleId THEN s.GrossSales * (a.SplitPercentage / 100) ELSE (s.GrossSales * (a.SplitPercentage / 100)) + ISNULL(s2.GrossSales,0) END As GrossSales
	, 0 Price
	, 1
	, s.BonusBuyNumber
	, 0
From ArticleSales s
INNER Join Articles a on a.Id = @SourceArticleId OR a.Id IN (Select Id from dbo.Split(@ArticleIds, ','))
LEFT Join ArticleSales s2 on s2.ArticleId = a.Id 
	and s2.BonusBuyNumber = s.BonusBuyNumber 
	and s2.StoreId = s.StoreId 
	and s2.Date = s.Date 
	and s.Confirmed = 1
	and (s2.Deleted is null or s2.Deleted = 0)
Where 
	s.ArticleId = @SourceArticleId
	and s.Confirmed = 1 
	and (s.Deleted is null or s.Deleted = 0)
Order By a.Id, s.BonusBuyNumber, s.Date

--Borro las ventas anteriores
Update ArticleSales
Set Confirmed = 0, Deleted = 1
From ArticleSales s
Inner Join @salesTable s2 on s2.ArticleId = s.ArticleId
	and s2.BonusBuyNumber = s.BonusBuyNumber 
	and s2.StoreId = s.StoreId 
	and s2.Date = s.Date 
	and s.Confirmed = 1
	
--Inserto las nuevas ventas
INSERT INTO ArticleSales (StoreId, ArticleId, Date, Units, Sales, GM, GrossSales, Price, Confirmed, BonusBuyNumber, Deleted)
Select StoreId, ArticleId, Date, Units, Sales, GM, GrossSales, Price, Confirmed, BonusBuyNumber, Deleted From @salesTable

--Genero el Audit Trail
INSERT INTO AuditTrails ([Source],[Date],[Comments])
Select 'ArticleMasterSplit', 
	   GETDATE(), 
	   'Se agrego el artículo ' + Code + ' ' + Name + ' desdoblando en un ' + convert(varchar, SplitPercentage) + '% al articulo ' + (Select Code + ' ' + Name From Articles Where Id = @SourceArticleId)  
From Articles 
Where Id in (Select Id from dbo.Split(@ArticleIds, ','))

END